<?php
if (!defined('ABSPATH')) {
    exit;
}

class NX_Custom_Sort_DB {
    public static function create_tables() {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        // Table for sorting rules
        $table_name = $wpdb->prefix . 'nx_custom_sort_rules';
        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            rule_name VARCHAR(255) NOT NULL,
            post_type VARCHAR(50) NOT NULL,
            category_slug VARCHAR(255),
            slugs TEXT,
            sort_order LONGTEXT NOT NULL,
            created_at DATETIME NOT NULL,
            PRIMARY KEY (id)
        ) $charset_collate;";
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql);

        // Table for visit tracking
        $table_name = $wpdb->prefix . 'nx_custom_sort_visits';
        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            page_id BIGINT(20) UNSIGNED NOT NULL,
            visit_time DATETIME NOT NULL,
            PRIMARY KEY (id),
            KEY page_id (page_id)
        ) $charset_collate;";
        dbDelta($sql);
    }

    public static function save_rule($rule_name, $post_type, $category_slug, $slugs, $sort_order) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'nx_custom_sort_rules';
        return $wpdb->insert(
            $table_name,
            [
                'rule_name' => $rule_name,
                'post_type' => $post_type,
                'category_slug' => $category_slug,
                'slugs' => maybe_serialize($slugs),
                'sort_order' => maybe_serialize($sort_order),
                'created_at' => current_time('mysql')
            ],
            ['%s', '%s', '%s', '%s', '%s', '%s']
        );
    }

    public static function get_rule($rule_id) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'nx_custom_sort_rules';
        $rule = $wpdb->get_row($wpdb->prepare("SELECT * FROM $table_name WHERE id = %d", $rule_id));
        if ($rule) {
            $rule->slugs = maybe_unserialize($rule->slugs);
            $rule->sort_order = maybe_unserialize($rule->sort_order);
        }
        return $rule;
    }

    public static function log_visit($page_id) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'nx_custom_sort_visits';
        return $wpdb->insert(
            $table_name,
            [
                'page_id' => $page_id,
                'visit_time' => current_time('mysql')
            ],
            ['%d', '%s']
        );
    }

    public static function get_visit_count($page_id) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'nx_custom_sort_visits';
        return $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM $table_name WHERE page_id = %d", $page_id));
    }
}

?>
